from openpyxl import load_workbook
from db_helper.mysql_database_helper import MySqlConnectionPool
import configparser

config_path = './mysql.ini'


def pool_init(path="./config.ini", section="mysql-209-lng2"):
    """
    初始化mysql
    """
    config = configparser.ConfigParser()
    # 读取配置文件
    config.read(path)
    host = config.get(section, 'host')
    port = config.get(section, 'port')
    database = config.get(section, 'database')
    username = config.get(section, 'username')
    password = config.get(section, 'password')
    init_size = int(config.get(section, 'init-size'))
    pool = MySqlConnectionPool(host=host, port=port, username=username, password=password,
                               database_name=database, pool_size=init_size)
    return pool


file_path = './excel/tank_data(1).xlsx'
sheet_name = 'TGGU2000019'
workbook = load_workbook(filename=file_path)
sheet = workbook[sheet_name]
mysql_pool = pool_init(config_path)
for row in sheet.iter_rows(min_row=2, max_col=8, values_only=True):
    sql = f"""
        insert into tank_data(make_num, terminal_id, data_submit_time, weight, weight_unit, volume_percentage, pressure, temperature,
        gps_longitude, gps_latitude, del_flag, trial_data) values ('{row[0]}', '{row[0]}_none', '{row[1]}', '{row[2]}', 't',
        '{row[3]}', '{row[4]}', '{row[5]}', '{row[6]}', '{row[7]}', -1, -1)
        """
    mysql_pool.exec_sql(sql)

